<?php
/**
 * @author shutyuka@gmail.com
 * @modified 2011-02-20
 */
class Preengage{
	
	static public $FLAG_INACTIVE = 0;
	
	static public $FLAG_UNACTIVE = 10;
	
	static public $TABLE_NAME="preengage";
	static public $TABLE_NAME_DEL="del_preengage";

	static public function Create($userId, $serviceId, $shopId, $startTime, $endTime, $money, $staffId, $deviceId, $designation = 0, $adminCreate = 0, $opId = 0, $backUp = null, $telNo = "", $name ="") {
		$preengage = array();
		if( $userId < 1 ){
			$userId = 0;
		}
		$preengage['user_id'] = $userId;
		
		$preengage['service_id'] = $serviceId;
		
		
		$preengage['shop_id'] = $shopId;
		$preengage['service_start_time'] = $startTime;
		$preengage['service_end_time'] = $endTime;
		$tempService = Service::GetById($serviceId);
		$preengage['real_end_time'] = $endTime + $tempService['reset_device_time'] * 60;
		$preengage['money'] = $money;
		
		$preengage['designation'] = $designation;
		
		$preengage['designation_stuff'] = $staffId;
		$preengage['device_id'] = $deviceId;
		
		if($telNo != "" && $telNo != null){
			$preengage['back1'] = $telNo;
			$preengage['back2'] = $name;
		}
		
		$preengage['create_time'] = time();
		$preengage['id'] = DB::Insert(self::$TABLE_NAME, $preengage);
		
		if( $adminCreate != 0 ){
			if( $backUp != null ){
				AccessHistory::ChangeObject($opId, $backUp, $preengage, self::$TABLE_NAME);
			}else{
				AccessHistory::AddObject($opId, $preengage, self::$TABLE_NAME);
			}
		}
		
		return $preengage['id'];
	}
	
	static public function CreateByBackUp($preengage) {
		$preengage['id'] = DB::Insert(self::$TABLE_NAME, $preengage);
		
		return $preengage['id'];
	}
	
	static public function GetOrderCount($shopId, $startMonth, $endDay, $orderYear){
		//date_default_timezone_set('Asia/Tokyo');
		$orderStartTime = mktime(0, 0, 0, $orderMonth, $orderDay, $orderYear);
		$orderEndTime = mktime(0, 0, 0, $orderMonth, $orderDay + 1, $orderYear);
		$oc = array( 
			'shop_id' => $shopId, 
			"service_start_time >= $orderStartTime",
			"real_end_time <= $orderEndTime",
		);
		
		$sql = "select count(1) as service_count from ".self::$TABLE_NAME.
		" where shop_id=$shopId and service_start_time>=$startTime and real_end_time <= $endTime" ;
		
		return DB::GetQueryResult($sql, false);
		
	}
	
	static public function GetAllOrderCount($shopId, $serviceId, $startYear, $startMonth, $startDay, $endYear, $endMonth, $endDay){
		//date_default_timezone_set('Asia/Tokyo');
		$orderStartTime = mktime(0, 0, 0, $startMonth, $startDay, $startYear);
		$orderEndTime = mktime(23, 59, 59, $endMonth, $endDay, $endYear);
		
		
		$sql = "select shop_id, service_id, count(1) as service_count from ".self::$TABLE_NAME.
		" where service_start_time>=$orderStartTime and real_end_time <= $orderEndTime and shop_id=$shopId and service_id=$serviceId".
		" group by shop_id, service_id" ;
		//Logs::setLog("||========$sql=========||");
		return DB::GetQueryResult($sql, false);
		
	}
	
	static public function GetAllOrderCountGroupByStaffId($staffId, $serviceId, $startYear, $startMonth, $startDay, $endYear, $endMonth, $endDay){
		//date_default_timezone_set('Asia/Tokyo');
		$orderStartTime = mktime(0, 0, 0, $startMonth, $startDay, $startYear);
		$orderEndTime = mktime(23, 59, 59, $endMonth, $endDay, $endYear);
		
		
		$sql = "select service_id, count(1) as service_count from ".self::$TABLE_NAME.
		" where service_start_time>=$orderStartTime and real_end_time <= $orderEndTime and designation_stuff=$staffId and service_id=$serviceId".
		" group by designation_stuff, service_id" ;
		//Logs::setLog(date_default_timezone_get()."||========$sql=========||$startMonth, $startDay, $startYear,,,,,$endMonth, $endDay, $endYear");
		return DB::GetQueryResult($sql, false);
		
	}
	
	static public function GetOrderedCountByUserId($userId, $serviceId, $startYear, $startMonth, $startDay, $endYear, $endMonth, $endDay){
		//date_default_timezone_set('Asia/Tokyo');
		$orderStartTime = mktime(0, 0, 0, $startMonth, $startDay, $startYear);
		$orderEndTime = mktime(23, 59, 59, $endMonth, $endDay, $endYear);
		
		
		$sql = "select service_id, count(1) as service_count from ".self::$TABLE_NAME.
		" where service_start_time>=$orderStartTime and real_end_time <= $orderEndTime and user_id=$userId and service_id=$serviceId".
		" group by user_id, service_id" ;
		//Logs::setLog(date_default_timezone_get()."||========$sql=========||$startMonth, $startDay, $startYear,,,,,$endMonth, $endDay, $endYear");
		//Logs::setLog( date("y-m-d H:i:s", $orderStartTime)."||".date("y-m-d H:i:s", $orderEndTime) );
		return DB::GetQueryResult($sql, true);
		
	}
	
	static public function Update($id, $userId, $serviceId, $shopId, $startTime, $endTime, $money, $staffId, $deviceId) {
		$preengage = array();
		$preengage['user_id'] = $userId;
		
		$preengage['service_id'] = $serviceId;
		$preengage['shop_id'] = $shopId;
		$preengage['service_start_time'] = $startTime;
		$preengage['service_end_time'] = $endTime;
		$preengage['money'] = $money;
		if($staffId < 1){
			$preengage['designation'] = 0;
		}else{
			$preengage['designation'] = 1;
		}
		$preengage['designation_stuff'] = $staffId;
		$preengage['device_id'] = $deviceId;
		$tempService = Service::GetById($serviceId);
		$preengage['real_end_time'] = $endTime + $tempService['reset_device_time'] * 60;
		
		$preengage['id'] = $id;
		
		Table::UpdateCache(self::$TABLE_NAME, $preengage['id'], $preengage);
		
		return ;
	}
	
	static public function UpdateByOrige($preengage) {	
		
		Table::UpdateCache(self::$TABLE_NAME, $preengage['id'], $preengage);
		
		return ;
	}

	static public function SetPreengageUnactive($preengageId) {
		if (!$preengageId) 
			return array();
		Table::UpdateCache(self::$TABLE_NAME, $preengageId, array(
			'activity' => self::$FLAG_UNACTIVE)
		);
		return ;
	}
	
	
	static public function SetPreengageInActive($preengageId) {
		if (!$preengageId) 
			return array();
		Table::UpdateCache(self::$TABLE_NAME, $preengageId, array(
			'activity' => self::$FLAG_INACTIVE)
		);
		return ;
	}
	
	static public function GetAllByUserId($userId, $startTime, $endTime){
		$oc = array( 
			'user_id' => $userId,
			"service_start_time>=$startTime",
			"service_end_time<=$endTime"
			);
		$all = DB::LimitQuery(self::$TABLE_NAME, array(
				'condition' => $oc,
				'order' => 'ORDER BY id ASC'
				));
		return $all;
	}
	
	
	static public function GetById($id) {
		if (!$id) return array();
		return DB::GetTableRow(self::$TABLE_NAME, array('id' => $id));
	}
	
	static public function GetByTimeAndService($startTime, $endTime, $serviceId, $staffId = 0){
		$sql = "select * from ".self::$TABLE_NAME.
		" where !(service_start_time<$orderStartTime or real_end_time>$orderEndTime) and service_id=$serviceId" ;
		if( $staffId > 0 ){
			$sql = $sql." and designation_stuff=$staffId ";
		}
		
		return DB::GetQueryResult($sql, false);
	}


	static public function GetListByUserIdAndShopId($userId, $shopId){
		$oc = array( 
			'user_id' => $userId,
			'shop_id' => $shopId
			);
		$all = DB::LimitQuery(self::$TABLE_NAME, array(
				'condition' => $oc,
				'order' => 'ORDER BY id ASC'
				));
		return $all;
	}
	
	
	static public function GetListShopId($shopId, $year, $month, $day, $startHour, $startMinute, $endHour, $endMinute){
		//date_default_timezone_set('Asia/Tokyo');
		$startTime = mktime($startHour, $startMinute, 0, $month, $day, $year);
		$endTime = mktime($endHour, $endMinute, 0, $month, $day, $year);
		
		$oc = array(
			'shop_id' => $shopId,
			"service_start_time < $endTime",
			"real_end_time > $startTime"
			);
		$all = DB::LimitQuery(self::$TABLE_NAME, array(
				'condition' => $oc,
				'order' => 'ORDER BY id ASC'
				));
		return $all;
	}
	
	static public function GetOrderedCount($shopId, $serviceId, $year, $month, $day, $startHour, $startMinute, $endHour, $endMinute){
		//date_default_timezone_set('Asia/Tokyo');
		$startTime = mktime($startHour, $startMinute, 0, $month, $day, $year);
		$endTime = mktime($endHour, $endMinute, 0, $month, $day, $year);
		
		$sql = "select count(1) as count from ".self::$TABLE_NAME." where shop_id=$shopId and ";
		if( $serviceId > 0 ){
			$sql = $sql."service_id=$serviceId and";
		}
		$sql = $sql." !($endTime <= service_start_time or $startTime >= real_end_time) " ;
		//Logs::setLog("new sql is || $sql ||");
		return DB::GetQueryResult($sql, false);
	}
	
	static public function GetListShopIdAndStaffId($shopId, $year, $month, $day, $startHour, $startMinute, $endHour, $endMinute, $staffId){
		//date_default_timezone_set('Asia/Tokyo');
		$startTime = mktime($startHour, $startMinute, 0, $month, $day, $year);
		$endTime = mktime($endHour, $endMinute, 0, $month, $day, $year);
		
		$oc = array(
			'shop_id' => $shopId,
			'designation_stuff' => $staffId,
			"service_start_time >= $startTime",
			"real_end_time <= $endTime"
			);
		$all = DB::LimitQuery(self::$TABLE_NAME, array(
				'condition' => $oc,
				'order' => 'ORDER BY service_start_time ASC'
				));
		return $all;
	}
	
	static public function GetListByUserId($userId, $startYear, $startMonth, $startDay, $endYear, $endMonth, $endDay){
		//date_default_timezone_set('Asia/Tokyo');
		$orderStartTime = mktime(0, 0, 0, $startMonth, $startDay, $startYear);
		$orderEndTime = mktime(23, 59, 59, $endMonth, $endDay, $endYear);
		
		$oc = array(
			'user_id' => $userId,
			"service_start_time >= $orderStartTime",
			"service_end_time <= $orderEndTime"
			);
		$all = DB::LimitQuery(self::$TABLE_NAME, array(
				'condition' => $oc,
				'order' => 'ORDER BY service_start_time ASC'
				));
		return $all;
	}
	
	static public function GetDayListBySS($shopId, $serviceId, $year, $month, $day){
		//date_default_timezone_set('Asia/Tokyo');
		$startTime = mktime(0, 0, 0, $month, $day, $year);
		$endTime = mktime(23, 59, 59, $month, $day, $year);
		
		$oc = array(
			'shop_id' => $shopId,
			'service_id' => $serviceId,
			"service_start_time >= $startTime",
			"real_end_time <= $endTime"
			);
		//Logs::setLog("for index".date_default_timezone_get()."||$year, $month, $day,,,,,$year, $month, $day");
		//Logs::setLog( "for index".date("y-m-d H:i:s", $startTime)."||".date("y-m-d H:i:s", $endTime)."<$startTime><$endTime>" );
		$all = DB::LimitQuery(self::$TABLE_NAME, array(
				'condition' => $oc,
				'order' => 'ORDER BY service_start_time ASC'
				));
		return $all;
	}
	
	static public function GetListByHours($shopId, $year, $month, $day, $startHour){
		//date_default_timezone_set('Asia/Tokyo');
		$startTime = mktime($startHour, 0, 0, $month, $day, $year);
		$endTime = mktime($startHour + 1, 0, 0, $month, $day, $year);
		
		$oc = array(
			'shop_id' => $shopId,
			"service_start_time >= $startTime",
			"service_start_time < $endTime"
			);
		$all = DB::LimitQuery(self::$TABLE_NAME, array(
				'condition' => $oc,
				'order' => 'ORDER BY service_start_time ASC'
				));
		return $all;
	}
	
	static public function HasOrdered($startTime, $endTime, $staffId) {
		$sql = "select * from preengage where designation_stuff=$staffId and !(real_end_time < $startTime or service_start_time > $endTime ) order by service_start_time";
		//Logs::setLog($sql);
		$temp = DB::GetQueryResult($sql, false);
		return $temp;
	}
	
	static public function HasOrderedSuchService($startTime, $endTime, $staffId, $serviceId) {
		$sql = "select * from preengage where designation_stuff=$staffId and !(real_end_time < $startTime or service_start_time > $endTime ) and service_id=$serviceId order by service_start_time";
		//Logs::setLog($sql);
		$temp = DB::GetQueryResult($sql, false);
		return $temp;
	}
	
	static public function DeleteById($id, $opId){
		$delTemp = self::GetById($id);
		$delTemp['op_id'] = $opId;
		$delTemp['create_time'] = time();
		DB::Insert(self::$TABLE_NAME_DEL, $delTemp);
		
		$sql = "delete from ".self::$TABLE_NAME." where id=$id" ;
		//Logs::setLog("new sql is || $sql ||");
		return DB::GetQueryResult($sql, false);
	}
	
	static public function GetDelList(/*$userId, */$startTime, $endTime){
		$oc = array( /*
			'user_id' => $userId,*/
			"service_start_time>=$startTime",
			"service_end_time<=$endTime"
			);
		$all = DB::LimitQuery(self::$TABLE_NAME_DEL, array(
				'condition' => $oc,
				'order' => 'ORDER BY id ASC'
				));
		return $all;
	}
	
	
	static public function GetStaticByShopId($startTime, $endTime){		
		
		$sql = "select shop_id, count(1) as count from ".self::$TABLE_NAME.
		" where service_start_time>=$startTime and real_end_time <= $endTime group by shop_id" ;
		//Logs::setLog("||===".date("y-m-d H:i", $startTime)."||".date("y-m-d H:i", $endTime)."=====$sql=========||");
		return DB::GetQueryResult($sql, false);
		
	}
	
	static public function GetStaticByServiceId($startTime, $endTime){		
		
		$sql = "select shop_id, service_id, count(1) as count from ".self::$TABLE_NAME.
		" where service_start_time>=$startTime and real_end_time <= $endTime group by shop_id, service_id" ;
		//Logs::setLog("||========$sql=========||");
		return DB::GetQueryResult($sql, false);
		
	}
	
	static public function GetStaticByStaffId($startTime, $endTime){	
		
		$sql = "select shop_id, designation_stuff, count(1) as count from ".self::$TABLE_NAME.
		" where service_start_time>=$startTime and real_end_time <= $endTime group by shop_id, designation_stuff" ;
		//Logs::setLog("||========$sql=========||");
		return DB::GetQueryResult($sql, false);
		
	}
	
static public function GetDesignationedStaticByStaffId($startTime, $endTime){	
		
		$sql = "select shop_id, designation_stuff, count(1) as count from ".self::$TABLE_NAME.
		" where service_start_time>=$startTime and real_end_time <= $endTime and designation=1 group by shop_id, designation_stuff" ;
		//Logs::setLog("||========$sql=========||");
		return DB::GetQueryResult($sql, false);
		
	}
	
	static public function GetStaticByUserId($startTime, $endTime){	
		
		$sql = "select shop_id, user_id, count(1) as count from ".self::$TABLE_NAME.
		" where service_start_time>=$startTime and real_end_time <= $endTime group by shop_id, user_id" ;
		//Logs::setLog("||========$sql=========||");
		return DB::GetQueryResult($sql, false);
		
	}
	
	static public function GetStaticByTelNo($startTime, $endTime){	
		
		$sql = "select shop_id, back1, count(1) as count from ".self::$TABLE_NAME.
		" where service_start_time>=$startTime and real_end_time <= $endTime and user_id=0 group by shop_id, back1" ;
		//Logs::setLog("||========$sql=========||");
		return DB::GetQueryResult($sql, false);
		
	}
	
	static public function GetServiceStaticByShopId($shopId, $serviceId, $startTime, $endTime){		
		
		$sql = "select shop_id, service_id, count(1) as count from ".self::$TABLE_NAME.
		" where shop_id=$shopId and service_id=$serviceId and service_start_time>=$startTime and real_end_time <= $endTime".
		" group by shop_id, service_id" ;
		//Logs::setLog("||========$sql=========||");
		return DB::GetQueryResult($sql, true);
		
	}
	
	static public function GetServiceStaticByStaffId($staffId, $serviceId, $startTime, $endTime, $shopId){		
		
		$sql = "select shop_id, service_id, count(1) as count from ".self::$TABLE_NAME.
		" where designation_stuff=$staffId and service_id=$serviceId and shop_id=$shopId and service_start_time>=$startTime and real_end_time <= $endTime".
		" group by designation_stuff, service_id, shop_id" ;
		//Logs::setLog("||========$sql=========||");
		return DB::GetQueryResult($sql, true);
		
	}
	
static public function GetDesigationedStaticByStaffId($staffId, $serviceId, $startTime, $endTime, $shopId){		
		
		$sql = "select shop_id, service_id, count(1) as count from ".self::$TABLE_NAME.
		" where designation_stuff=$staffId and service_id=$serviceId and shop_id=$shopId and service_start_time>=$startTime and real_end_time <= $endTime".
		" and designation=1 ".
		" group by designation_stuff, service_id, shop_id" ;
		//Logs::setLog("||========$sql=========||");
		return DB::GetQueryResult($sql, true);
		
	}
	
	static public function GetServiceStaticByUserId($userId, $serviceId, $startTime, $endTime, $shopId){		
		
		$sql = "select shop_id, service_id, count(1) as count from ".self::$TABLE_NAME.
		" where user_id=$userId and service_id=$serviceId and shop_id=$shopId and service_start_time>=$startTime and real_end_time <= $endTime".
		" group by user_id, service_id, shop_id" ;
		//Logs::setLog("||========$sql=========||");
		return DB::GetQueryResult($sql, true);
		
	}
	
	static public function GetServiceStaticByTelNo($telNo, $serviceId, $startTime, $endTime, $shopId){		
		
		$sql = "select shop_id, service_id, count(1) as count from ".self::$TABLE_NAME.
		" where back1='".$telNo."' and service_id=$serviceId and shop_id=$shopId and service_start_time>=$startTime and real_end_time <= $endTime".
		" group by back1, service_id, shop_id" ;
		//Logs::setLog("||========$sql=========||");
		return DB::GetQueryResult($sql, true);
		
	}
	
	static public function GetStaticCount($userId, $serviceId = 0){	
		
		$sql = "select shop_id, user_id, count(1) as count from ".self::$TABLE_NAME.
		" where user_id=$userId " ;
		if( $serviceId > 0 ){
			$sql = $sql." and service_id=$serviceId";
		}
		$sql = $sql." group by user_id";
	if( $serviceId > 0 ){
			$sql = $sql.", service_id";
		}
		//Logs::setLog("||========$sql=========||");
		return DB::GetQueryResult($sql, true);
		
	}
	/*static public function Get($shopId, $serviceId, $startYear, $startMonth, $startDay, $endYear, $endMonth, $endDay){
		$orderStartTime = mktime(0, 0, 0, $startMonth, $startDay, $startYear);
		$orderEndTime = mktime(23, 59, 59, $endMonth, $endDay, $endYear);
		
		
		$sql = "select shop_id, service_id, count(1) as service_count from ".self::$TABLE_NAME.
		" where service_start_time>=$orderStartTime and service_end_time <= $orderEndTime and shop_id=$shopId and service_id=$serviceId" ;
		//Logs::setLog("||========$sql=========||");
		return DB::GetQueryResult($sql, false);
		
	}*/
	
	static public function CheckOtherStaff($startTimeq, $endTime,$deviceId,$login_user_id, $serviceId, $defaultShopId, $startTime, $endTime, $service_price, $staffId, $_staff_id) {
		//できる人。YES
		$sql = <<<EOQ
			select * from staff_skill where service_id = $deviceId and staff_id in
			(select designation_stuff from preengage where 
				!(real_end_time < $startTimeq or service_start_time > $endTime ) and back1='YES' order by id)
EOQ;
		//Logs::setLog($sql);
		//dekisouna hito
		$dekisou_sql = DB::GetQueryResult($sql, false);
		for($i = 0;count($dekisou_sql) > $i ; $i++ ){
			$kari_staffid = $dekisou_sql[$i]['staff_id'];
			$sql = <<<EOQ
				select * from preengage where 
					!(real_end_time < $startTimeq or service_start_time > $endTime ) and back1='YES' and
						 designation_stuff = $kari_staffid order by id
EOQ;
			$sqls = DB::GetQueryResult($sql, false);
			$sql_staffid = $sqls[0]['designation_stuff'];
			$sql_id = $sqls[0]['id'];
			$sql = <<<EOQ
				select * from staff_skill where service_id = $deviceId and staff_id not in 
				(select designation_stuff from preengage where 
					(service_start_time > $startTimeq and service_end_time < $endTime) or
					(service_start_time between $startTimeq and $endTime) or 
					(service_end_time between $startTimeq and $endTime)
					 and designation_stuff = $sql_staffid
						 order by id)
EOQ;

			$change_member_id = DB::GetQueryResult($sql, false);
			if(count($change_member_id)!=0){
				$change_member_id = $change_member_id[0]['staff_id'];
				$sql = <<<EOQ
					update preengage set designation_stuff = $change_member_id where id = $sql_id
EOQ;
				$sql = DB::GetQueryResult($sql, false);
				//この$staffIdで登録
				Preengage::Create($login_user_id, $serviceId, $defaultShopId, $startTime, $endTime, $service_price, $kari_staffid, $deviceId, $_staff_id,0,0,null,"YES");
				Session::Set('msg', 'ご予約有難う御座いました。ご来店待ちしております。');
				$check_touroku = "YES";
				break;
			}else{

			}
		}
	if($check_touroku != "YES")
		Session::Set('error', '大変申し訳ございません。お客様ご予約したい時間帯は込み合っている為に予約取れませんでした。');
		
		return $temp;
	}
}
